/* GSOrderApplyDao.java
 *
 * Project/Product: GWBS2009
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   Administrator	2009-3-26	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2009
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package com.hitrust.gs.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;

import system.LoginContext;

import com.hitrust.gs.bean.GSOrderAnaDetailAndCategoryBean;
import com.hitrust.gs.bean.GSOrderApplyBean;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.vo.GSBusOrderCancelVO;
/**
 * 处理补货申请
 * **/
public class GSOrderApplyDao extends GSBaseDAO {
    
    private String getSql(){
        StringBuffer result=new StringBuffer();
        result.append(" SELECT a.*, pro.adapt_machine_type, pro.appender_property,");
        result.append("pro.area, pro.balance_customer_code, pro.balance_mode,");
        result.append("pro.category_code,");
        result.append("pro.create_time AS create_time_product,");
        result.append("pro.create_user AS create_user_product,");
        result.append("pro.create_user_id AS create_user_id_product, pro.drawing_piece_code,");
        result.append("pro.ID AS id_product, pro.is_buy, pro.is_normal,");
        result.append("pro.is_tradeflat_product,");
        result.append("pro.last_update_time AS last_update_time_product,");
        result.append("pro.last_update_user AS last_update_user_product,");
        result.append("pro.last_update_user_id AS last_update_user_id_product,");
        result.append("pro.manufaturer, pro.model, pro.NAME AS name_product,");
        result.append("pro.old_apep_code, pro.pic, pro.product_brand, pro.product_unit,");
        result.append("pro.quality_assure_period,");
        result.append("pro.recent_adjusted_price, pro.recent_adjustprice_date,");
        result.append("pro.record_date, pro.recorder_id, pro.remark AS remark_product,");
        result.append("pro.spec, pro.status AS status_product, pro.unit_price,");
        result.append("prv.NAME AS name_provider ");
        result.append("FROM t_order_apply a, t_product pro, t_provider prv,T_PRODUCT_CATEGORY cat ");
        result.append("where a.APEP_CODE=pro.APEP_CODE and a.CONCENTRICITY_CODE=pro.CONCENTRICITY_CODE and a.PROVIDER_CODE=prv.CODE ");
        result.append(" and pro.CATEGORY_CODE=cat.code ");
        return result.toString();
    }
    private String getViewSql(GSBusOrderCancelVO aGSBusOrderCancelVO){
        StringBuffer tSql=new StringBuffer(this.getSql());
        if (aGSBusOrderCancelVO.orderid != null) {
            tSql.append(" and a.APPLY_CODE like '%" + aGSBusOrderCancelVO.orderid + "%'");
        }
        if (!aGSBusOrderCancelVO.orderDetailStatus.equals("00")) {
            tSql.append(" and a.APPLY_STATUS = '" + aGSBusOrderCancelVO.orderDetailStatus + "'");
        }
        if (aGSBusOrderCancelVO.orderDeptId != null) {
                tSql.append(" and a.WAREHOUSE_CODE  like '" + aGSBusOrderCancelVO.orderDeptId + "%'");
        }
        if (aGSBusOrderCancelVO.concentricityId != null) {
            tSql.append(" and a.CONCENTRICITY_CODE like '" + aGSBusOrderCancelVO.concentricityId + "%'");
        }
        if (aGSBusOrderCancelVO.manufInfo != null) {
            tSql.append(" and (pro.NAME like '%" + aGSBusOrderCancelVO.manufInfo + "%'");
            tSql.append(" or pro.MODEL like '%" + aGSBusOrderCancelVO.manufInfo + "%'");
            tSql.append(" or pro.SPEC like '%" + aGSBusOrderCancelVO.manufInfo + "%'");
            tSql.append(" or pro.PRODUCT_BRAND like '%" + aGSBusOrderCancelVO.manufInfo + "%'");
            tSql.append(" or pro.MANUFATURER like '%" + aGSBusOrderCancelVO.manufInfo + "%')");
        }
        if (aGSBusOrderCancelVO.providerId != null) {
            tSql.append(" and prv.NAME like '%" + aGSBusOrderCancelVO.providerId + "%'");
        }
        if (aGSBusOrderCancelVO.manufType1!=null) {
            if(aGSBusOrderCancelVO.manufType1.equals("0")){
                tSql.append(" and cat.code like '%'");
            }else if(aGSBusOrderCancelVO.manufType2.equals("0")){
                tSql.append(" and (cat.CATEGORY_ID1 ='" + aGSBusOrderCancelVO.manufType1 + "'");
                tSql.append(" or cat.code ='" + aGSBusOrderCancelVO.manufType1 + "')");
            }else if(aGSBusOrderCancelVO.manufType3.equals("0")){
                tSql.append(" and (cat.CATEGORY_ID2 ='" + aGSBusOrderCancelVO.manufType2 + "'");
                tSql.append(" or cat.code ='" + aGSBusOrderCancelVO.manufType2 + "')");
            }else{
                tSql.append(" and cat.code ='" + aGSBusOrderCancelVO.manufType3 + "'");
            }
        }
        tSql.append(" and to_char(to_date(a.APPLY_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYYMMDD') >='"
                + aGSBusOrderCancelVO.orderCreateDateStr + "'");
        tSql.append(" and to_char(to_date(a.APPLY_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYYMMDD') <='"
                + aGSBusOrderCancelVO.orderCreateDateEnd + "'");
        tSql.append(" order by a.APPLY_DATE desc");
        return tSql.toString();
    }
    public boolean  saveOrderApply(ArrayList applyList) throws Exception{
        String sql="insert into T_ORDER_APPLY(ID,APPLY_CODE,APEP_CODE,CONCENTRICITY_CODE,WAREHOUSE_CODE,"
                +"WAREHOUSE_NAME,PROVIDER_CODE,SEND_COUNT,SEND_DATE,SEND_TIME,APPLY_STATUS,APPLY_DATE,"
                +"LAST_UPDATE_USER_ID,LAST_UPDATE_USER,LAST_UPDATE_TIME "
                +"values((select sys_guid() from dual),?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement ps=null;
        Connection con=null;
        boolean bl=false;
        try {
            con=this.getConnetion();
            con.setAutoCommit(false);
            ps=con.prepareStatement(sql);
            for (int i = 0; i < applyList.size(); i++) {
                GSOrderApplyBean applyBean=(GSOrderApplyBean) applyList.get(i);
                ps.setString(1,applyBean.getApply_code());
                ps.setString(2, applyBean.getApep_code());
                ps.setString(3, applyBean.getConcentricity_code());
                ps.setString(4, applyBean.getWarehouse_code());
                ps.setString(5, applyBean.getWarehouse_name());
                ps.setString(6, applyBean.getProvider_code());
                ps.setDouble(7, applyBean.getSend_count());
                ps.setString(8, applyBean.getSend_date());
                ps.setString(9, applyBean.getSend_time());
                ps.setString(10, applyBean.getApply_status());
                ps.setString(11, applyBean.getApply_date());
                ps.setString(12, applyBean.getLast_update_user_id());
                ps.setString(13, applyBean.getLast_update_user());
                ps.setString(14, applyBean.getLast_update_time());
                ps.addBatch();
            }
            ps.executeBatch();
            con.commit();
            bl=true;
        } catch (Exception e) {
            con.rollback();
        }finally{
            close(con);
        }
        return bl;
    }
    public ArrayList getAllOrderApplyByPage(GSBusOrderCancelVO aGSBusOrderCancelVO,int pageNo,int pageSize) throws Exception{
        String sql=this.getViewSql(aGSBusOrderCancelVO);
        ArrayList tList = null;
        GSLogWriter.debug(this, sql);
        Object[] tObj = {};
        try {
            tList = this.executeQuery(new GSOrderApplyBean(), sql, tObj,pageNo,pageSize);
        } catch (Exception e) {
            GSLogWriter.error(this, e.toString());
            throw new GSException(GSException.E19999, GSException.E19999_MSG);
        }
        return tList;
    }
    public int getAllOrderApplyCount(GSBusOrderCancelVO aGSBusOrderCancelVO) throws Exception{
        String sql="select count(*) from ( "+this.getViewSql(aGSBusOrderCancelVO)+" )";
        Object[] tObj = {};
        int count=0;
        count=this.queryRowNum(sql, tObj);
        return count;
    }
    //根据id获取获取申请单信息
    public GSOrderApplyBean getOrderApplyBeanById(String aid)throws Exception{
        String sql=this.getSql()+" and a.id=?";
        Object[] tobj={aid};
        ArrayList tList=this.executeQuery(new GSOrderApplyBean(), sql, tobj);
        GSOrderApplyBean bean=null;
        if (tList.size()>0) {
            bean=(GSOrderApplyBean) tList.get(0);
        }
        return bean;
    }
    //更新申请单
    public boolean updateOrderApplyBean(Connection con,GSOrderApplyBean bean)throws Exception{
        String sql="update t_order_apply set apply_stauts=?,APPLY_NOAGREE_REASON=?,LAST_UPDATE_USER_ID=?,LAST_UPDATE_USER=?,LAST_UPDATE_TIME=? where id=?";
        Object[] tobj=new Object[6];
        tobj[0]=bean.getApply_status();
        tobj[1]=bean.getApply_noagree_reason();
        tobj[2]=bean.getLast_update_user_id();
        tobj[3]=bean.getLast_update_user();
        tobj[4]=bean.getLast_update_time();
        tobj[5]=bean.getId();
        boolean bl=this.executeUpdate(con,sql, tobj);
        return bl;
    }
}
